﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VPC.BO;
using System.Data.SqlClient;

namespace VPC.DL
{
    public class ContactDL
    {
        private static SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Public\\Documents\\VPC.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

        public ContactDL()
        {
        }

        public static List<ContactBO> FetchAllContactDetails()
        {
            List<ContactBO> lstContactDetails = new List<ContactBO>();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM contact_details2", conn);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    lstContactDetails.Add(
                          new ContactBO()
                          {

                              ContactId = Convert.ToInt32(rdr["ContactId"].ToString()),
                              FirstName = rdr["FirstName"].ToString(),
                              LastName = rdr["LastName"].ToString(),
                              Fonction = rdr["Fonction"].ToString(),
                              Compagny = rdr["Compagny"].ToString(),
                              MobileNumber1 = rdr["MobileNumber1"].ToString(),
                              MobileNumber2 = rdr["MobileNumber2"].ToString(),
                              MobileNumber3 = rdr["MobileNumber3"].ToString(),
                              HomeNumber1 = rdr["HomeNumber1"].ToString(),
                              HomeNumber2 = rdr["HomeNumber2"].ToString(),
                              HomeNumber3 = rdr["HomeNumber3"].ToString(),
                              WorkNumber1 = rdr["WorkNumber1"].ToString(),
                              WorkNumber2 = rdr["WorkNumber2"].ToString(),
                              WorkNumber3 = rdr["WorkNumber3"].ToString(),
                              FaxNumber1 = rdr["FaxNumber1"].ToString(),
                              FaxNumber2 = rdr["FaxNumber2"].ToString(),
                              FaxNumber3 = rdr["FaxNumber3"].ToString(),
                              Street = rdr["Street"].ToString(),
                              Locality = rdr["Locality"].ToString(),
                              Code = rdr["Code"].ToString(),
                              Country = rdr["Country"].ToString(),
                              Email1 = rdr["Email1"].ToString(),
                              Email2 = rdr["Email2"].ToString(),
                              Email3 = rdr["Email3"].ToString()

                          }
                      );

                }
                rdr.Close();
                conn.Close();

                return lstContactDetails;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static ContactBO FetchContactDetails(Int32 iContactId)
        {
            ContactBO objContactBO = new ContactBO();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM contact_details2 WHERE ContactId = @Id", conn);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@Id";
                param.Value = iContactId;

                cmd.Parameters.Add(param);
                SqlDataReader rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        objContactBO.ContactId = Convert.ToInt32(rdr["ContactId"].ToString());
                        objContactBO.FirstName = rdr["FirstName"].ToString();
                        objContactBO.LastName = rdr["LastName"].ToString();
                        objContactBO.Fonction = rdr["Fonction"].ToString();
                        objContactBO.Compagny = rdr["Compagny"].ToString();
                        objContactBO.MobileNumber1 = rdr["MobileNumber1"].ToString();
                        objContactBO.MobileNumber2 = rdr["MobileNumber2"].ToString();
                        objContactBO.MobileNumber3 = rdr["MobileNumber3"].ToString();
                        objContactBO.HomeNumber1 = rdr["HomeNumber1"].ToString();
                        objContactBO.HomeNumber2 = rdr["HomeNumber2"].ToString();
                        objContactBO.HomeNumber3 = rdr["HomeNumber3"].ToString();
                        objContactBO.WorkNumber1 = rdr["WorkNumber1"].ToString();
                        objContactBO.WorkNumber2 = rdr["WorkNumber2"].ToString();
                        objContactBO.WorkNumber3 = rdr["WorkNumber3"].ToString();
                        objContactBO.FaxNumber1 = rdr["FaxNumber1"].ToString();
                        objContactBO.FaxNumber2 = rdr["FaxNumber2"].ToString();
                        objContactBO.FaxNumber3 = rdr["FaxNumber3"].ToString();
                        objContactBO.Street = rdr["Street"].ToString();
                        objContactBO.Locality = rdr["Locality"].ToString();
                        objContactBO.Code = rdr["Code"].ToString();
                        objContactBO.Country = rdr["Country"].ToString();
                        objContactBO.Email1 = rdr["Email1"].ToString();
                        objContactBO.Email2 = rdr["Email2"].ToString();
                        objContactBO.Email3 = rdr["Email3"].ToString();
                    }
                }
                else
                {

                }

                rdr.Close();
                conn.Close();

                return objContactBO;
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        public static List<ContactBO> FetchContactDetails(string sFName)
        {
            List<ContactBO> lstContactDetails = new List<ContactBO>();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM contact_details2 WHERE FirstName = @FName OR LastName = @FName OR Compagny = @FName", conn);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@FName";
                param.Value = sFName;

                cmd.Parameters.Add(param);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    lstContactDetails.Add(
                          new ContactBO()
                          {
                              ContactId = Convert.ToInt32(rdr["ContactId"].ToString()),
                              FirstName = rdr["FirstName"].ToString(),
                              LastName = rdr["LastName"].ToString(),

                              Fonction = rdr["Fonction"].ToString(),
                              Compagny = rdr["Compagny"].ToString(),
                              MobileNumber1 = rdr["MobileNumber1"].ToString(),
                              MobileNumber2 = rdr["MobileNumber2"].ToString(),
                              MobileNumber3 = rdr["MobileNumber3"].ToString(),
                              HomeNumber1 = rdr["HomeNumber1"].ToString(),
                              HomeNumber2 = rdr["HomeNumber2"].ToString(),
                              HomeNumber3 = rdr["HomeNumber3"].ToString(),
                              WorkNumber1 = rdr["WorkNumber1"].ToString(),
                              WorkNumber2 = rdr["WorkNumber2"].ToString(),
                              WorkNumber3 = rdr["WorkNumber3"].ToString(),
                              FaxNumber1 = rdr["FaxNumber1"].ToString(),
                              FaxNumber2 = rdr["FaxNumber2"].ToString(),
                              FaxNumber3 = rdr["FaxNumber3"].ToString(),
                              Street = rdr["Street"].ToString(),
                              Locality = rdr["Locality"].ToString(),
                              Code = rdr["Code"].ToString(),
                              Country = rdr["Country"].ToString(),
                              Email1 = rdr["Email1"].ToString(),
                              Email2 = rdr["Email2"].ToString(),
                              Email3 = rdr["Email3"].ToString()

                          }
                      );

                }
                conn.Close();

                return lstContactDetails;
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        public static int UpdateContactDetails(ContactBO objContactBO)
        {
            try
            {
                conn.Open();

                Int32 iContactId = objContactBO.ContactId;
                string sFName = objContactBO.FirstName;
                string sLName = objContactBO.LastName;
                string sFonction = objContactBO.Fonction;
                string sCompagny = objContactBO.Compagny;
                string sMobileNumber1 = objContactBO.MobileNumber1;
                string sMobileNumber2 = objContactBO.MobileNumber2;
                string sMobileNumber3 = objContactBO.MobileNumber3;
                string sHomeNumber1 = objContactBO.HomeNumber1;
                string sHomeNumber2 = objContactBO.HomeNumber2;
                string sHomeNumber3 = objContactBO.HomeNumber3;
                string sWorkNumber1 = objContactBO.WorkNumber1;
                string sWorkNumber2 = objContactBO.WorkNumber2;
                string sWorkNumber3 = objContactBO.WorkNumber3;
                string sFaxNumber1 = objContactBO.FaxNumber1;
                string sFaxNumber2 = objContactBO.FaxNumber2;
                string sFaxNumber3 = objContactBO.FaxNumber3;
                string sStreet = objContactBO.Street;
                string sLocality = objContactBO.Locality;
                string sCode = objContactBO.Code;
                string sCountry = objContactBO.Country;
                string sEmail1 = objContactBO.Email1;
                string sEmail2 = objContactBO.Email2;
                string sEmail3 = objContactBO.Email3;

                SqlCommand cmd = new SqlCommand("UPDATE contact_details2 SET FirstName = @FName, LastName = @LName," +
                                                "Fonction = @Fonction , Compagny = @Compagny, MobileNumber1 = @MobileNumber1," +
                                                "MobileNumber2 = @MobileNumber2 , MobileNumber3 = @MobileNumber3, HomeNumber1 = @HomeNumber1," +
                                                "HomeNumber2 = @HomeNumber2 , HomeNumber3 = @HomeNumber3, WorkNumber1 = @WorkNumber1, " +
                                                "WorkNumber2 = @WorkNumber2 , WorkNumber3 = @WorkNumber3, FaxNumber1 = @FaxNumber1," +
                                                "FaxNumber2 = @FaxNumber2 , FaxNumber3 = @FaxNumber3, Street = @Street, Locality = @Locality," +
                                                "Code = @Code , Country = @Country, Email1 = @Email1, Email2 = @Email2," +
                                                "Email3 = @Email3 WHERE ContactId = @Id", conn);

                cmd.Parameters.Add(new SqlParameter("@Id", iContactId));
                cmd.Parameters.Add(new SqlParameter("@FName", sFName));
                cmd.Parameters.Add(new SqlParameter("@LName", sLName));
                cmd.Parameters.Add(new SqlParameter("@Fonction", sFonction));
                cmd.Parameters.Add(new SqlParameter("@Compagny", sCompagny));
                cmd.Parameters.Add(new SqlParameter("@MobileNumber1", sMobileNumber1));
                cmd.Parameters.Add(new SqlParameter("@MobileNumber2", sMobileNumber2));
                cmd.Parameters.Add(new SqlParameter("@MobileNumber3", sMobileNumber3));
                cmd.Parameters.Add(new SqlParameter("@HomeNumber1", sHomeNumber1));
                cmd.Parameters.Add(new SqlParameter("@HomeNumber2", sHomeNumber2));
                cmd.Parameters.Add(new SqlParameter("@HomeNumber3", sHomeNumber3));
                cmd.Parameters.Add(new SqlParameter("@WorkNumber1", sWorkNumber1));
                cmd.Parameters.Add(new SqlParameter("@WorkNumber2", sWorkNumber2));
                cmd.Parameters.Add(new SqlParameter("@WorkNumber3", sWorkNumber3));
                cmd.Parameters.Add(new SqlParameter("@FaxNumber1", sFaxNumber1));
                cmd.Parameters.Add(new SqlParameter("@FaxNumber2", sFaxNumber2));
                cmd.Parameters.Add(new SqlParameter("@FaxNumber3", sFaxNumber3));
                cmd.Parameters.Add(new SqlParameter("@Street", sStreet));
                cmd.Parameters.Add(new SqlParameter("@Locality", sLocality));
                cmd.Parameters.Add(new SqlParameter("@Code", sCode));
                cmd.Parameters.Add(new SqlParameter("@Country", sCountry));
                cmd.Parameters.Add(new SqlParameter("@Email1", sEmail1));
                cmd.Parameters.Add(new SqlParameter("@Email2", sEmail2));
                cmd.Parameters.Add(new SqlParameter("@Email3", sEmail3));


                int result = cmd.ExecuteNonQuery();

                conn.Close();

                return result;

            }
            catch (Exception e)
            {
                throw e;
            }

        }

        public static int AddNewContactDetails(ContactBO objContactBO)
        {

            try
            {
                conn.Open();

                Int32 iContactId = objContactBO.ContactId;
                string sFName = objContactBO.FirstName;
                string sLName = objContactBO.LastName;
                string sFonction = objContactBO.Fonction;
                string sCompagny = objContactBO.Compagny;
                string sMobileNumber1 = objContactBO.MobileNumber1;
                string sMobileNumber2 = objContactBO.MobileNumber2;
                string sMobileNumber3 = objContactBO.MobileNumber3;
                string sHomeNumber1 = objContactBO.HomeNumber1;
                string sHomeNumber2 = objContactBO.HomeNumber2;
                string sHomeNumber3 = objContactBO.HomeNumber3;
                string sWorkNumber1 = objContactBO.WorkNumber1;
                string sWorkNumber2 = objContactBO.WorkNumber2;
                string sWorkNumber3 = objContactBO.WorkNumber3;
                string sFaxNumber1 = objContactBO.FaxNumber1;
                string sFaxNumber2 = objContactBO.FaxNumber2;
                string sFaxNumber3 = objContactBO.FaxNumber3;
                string sStreet = objContactBO.Street;
                string sLocality = objContactBO.Locality;
                string sCode = objContactBO.Code;
                string sCountry = objContactBO.Country;
                string sEmail1 = objContactBO.Email1;
                string sEmail2 = objContactBO.Email2;
                string sEmail3 = objContactBO.Email3;

                SqlCommand cmd = new SqlCommand("INSERT INTO contact_details2 (FirstName, LastName, Fonction, Compagny, MobileNumber1, MobileNumber2, MobileNumber3, " +
                                                                              "HomeNumber1, HomeNumber2, HomeNumber3, WorkNumber1, WorkNumber2, WorkNumber3, FaxNumber1, FaxNumber2, FaxNumber3, " +
                                                                              "Street, Locality, Code, Country, Email1, Email2, Email3)" +
                                                " VALUES (@FName, @LName, @Fonction, @Compagny, @MobileNumber1, " +
                                                         "@MobileNumber2, @MobileNumber3, @HomeNumber1, @HomeNumber2, @HomeNumber3, " +
                                                         "@WorkNumber1, @WorkNumber2, @WorkNumber3, @FaxNumber1, @FaxNumber2, " +
                                                         "@FaxNumber3, @Street, @Locality, @Code, @Country, " +
                                                         "@Email1, @Email2, @Email3)", conn);

                cmd.Parameters.Add(new SqlParameter("@Id", iContactId));
                cmd.Parameters.Add(new SqlParameter("@FName", sFName));
                cmd.Parameters.Add(new SqlParameter("@LName", sLName));
                cmd.Parameters.Add(new SqlParameter("@Fonction", sFonction));
                cmd.Parameters.Add(new SqlParameter("@Compagny", sCompagny));
                cmd.Parameters.Add(new SqlParameter("@MobileNumber1", sMobileNumber1));
                cmd.Parameters.Add(new SqlParameter("@MobileNumber2", sMobileNumber2));
                cmd.Parameters.Add(new SqlParameter("@MobileNumber3", sMobileNumber3));
                cmd.Parameters.Add(new SqlParameter("@HomeNumber1", sHomeNumber1));
                cmd.Parameters.Add(new SqlParameter("@HomeNumber2", sHomeNumber2));
                cmd.Parameters.Add(new SqlParameter("@HomeNumber3", sHomeNumber3));
                cmd.Parameters.Add(new SqlParameter("@WorkNumber1", sWorkNumber1));
                cmd.Parameters.Add(new SqlParameter("@WorkNumber2", sWorkNumber2));
                cmd.Parameters.Add(new SqlParameter("@WorkNumber3", sWorkNumber3));
                cmd.Parameters.Add(new SqlParameter("@FaxNumber1", sFaxNumber1));
                cmd.Parameters.Add(new SqlParameter("@FaxNumber2", sFaxNumber2));
                cmd.Parameters.Add(new SqlParameter("@FaxNumber3", sFaxNumber3));
                cmd.Parameters.Add(new SqlParameter("@Street", sStreet));
                cmd.Parameters.Add(new SqlParameter("@Locality", sLocality));
                cmd.Parameters.Add(new SqlParameter("@Code", sCode));
                cmd.Parameters.Add(new SqlParameter("@Country", sCountry));
                cmd.Parameters.Add(new SqlParameter("@Email1", sEmail1));
                cmd.Parameters.Add(new SqlParameter("@Email2", sEmail2));
                cmd.Parameters.Add(new SqlParameter("@Email3", sEmail3));


                int result = cmd.ExecuteNonQuery();

                conn.Close();

                return result;
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        public static int DeleteContactDetails(Int32 iContactId)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("DELETE FROM contact_details2 WHERE ContactId = @Id", conn);

                cmd.Parameters.Add(new SqlParameter("@Id", iContactId));

                int result = cmd.ExecuteNonQuery();

                conn.Close();
                return result;

            }
            catch (Exception e)
            {
                throw e;
            }

        }
    }
}
